#!/usr/bin/python
#
#   pytunesconnect.py
#
#   Copyright Jonathan Carroll (jonathan.carroll@gmail.com)
#   Created On March 8, 2010
#
#   Version 1.0.1 - Initial release
#       Bundled with appdailysales.py version 1.10
#
#   Project website: http://code.google.com/p/pytunesconnect/
#
#   About pyTunesConnect:
#   This script uses the appdailysales.py project (http://appdailysales.googlecode.com/)
#   to download iTunes sales reports.  pyTunesConnect will then parse them, insert the
#   data into a MySQL database and provide a thin view layer to show the charts and stats.
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
# 
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
# 
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
# THE SOFTWARE.

###################
#Default settings
###################
#ip or hostname of the mysql database server
dbhost = "localhost"
#username to use when connecting to db
dbuser = "root"
#password to use when connecting to db
dbpass = "sekret"
#dbname to connect to
dbname = "sales_stats"
#table name in the database
dbtable = "daily"
#base URL where the pyTunesConnect file is hosted if using the built in view layer
baseurl = "http://www.mydomain.com/cgi-bin/pytunesconnect.py"
#Only access from these IPs will be allowed if using the built in view layer, this should be a list
allowedips = ["123.254.12.34","127.0.0.1"]



import MySQLdb
import cgi
import cgitb
import pytunescharts
cgitb.enable()
import os
import appdailysales
import sys
import traceback
import re
import datetime
from datetime import timedelta

""" The main pytunesconnect module
"""

class Pytunesconnect(object):
    """ The main pytunesconnect class.  You should create one instance of this
        object and pass all requests to it.

        All values passed in when initializing are optional.  If left blank they
        will default to the values set in the "Default Settings" section at the
        top of the pytunesconnect.py file.

        Variable descriptions (all are strings unless specified):\n
        host - The hostname or IP address of the server running the MySQL database.\n
        user - The user used to login to the database server specified by 'host'\n
        password - The password for the user connecting to the database.\n
        name - Name of the database to connect to on the 'host'.\n
        table - Name of the table to use in the database.\n
        url - Base URL where pytunesconnect.py is hosted, only needed if using view layer.\n
        ips - <type list> - IP addresses allowed to access the view layer.\n
    """
    def __init__(self, host=dbhost, user=dbuser, password=dbpass, name=dbname, table=dbtable, url=baseurl, ips=allowedips):
        """ The initializer
        """
        self.dbhost = host
        self.dbuser = user
        self.dbpass = password
        self.dbname = name
        self.dbtable = table
        self.baseurl = url
        self.allowedips = ips
        #create the database connection
        self.db = MySQLdb.connect(host=self.dbhost, user=self.dbuser, passwd=self.dbpass,db=self.dbname)
    def getItems(self):
        """ Returns a dicationary of vendor IDs and apple ids currently
        in the database.  The Key is the vendor id and the value is the apple
        identifier.

        The returned dictionary will be in the form {{'Facebook':'12345687'},{'Pandora':'1234568'}}
        """
        cursor = self.db.cursor()
        query = "SELECT DISTINCT(vendorid), appleidentifier FROM " + self.dbtable
        cursor.execute(query)
        result = cursor.fetchall()
        items = {}
        for item in result:
            items[str(item[0])] = str(item[1])
        return items
    def getItemDetail(self, appleid, offset="0"):
        """ Returns a list of lists containing information on the number of
        new downloads and reinstalls of a given application id (Apple ID).  Information is
        grouped by date for worldwide downloads in order by date. Offset controls
        how far back in time data is shown returned for, it should be a unix
        timestamp as a string or a date string in the form of YYYY-MM-DD.

        The first list returned in the list contains headers with names of
        info in the lists that follow it.

        Example return

        [["Date", "NewDownloads", "Reinstalls"],["2010-02-22", "200", "50"],["2010-02-23", "150", "25"]]
        """
        cursor = self.db.cursor()
        query = "SELECT a.begindate, a.dls as new, b.dls as reinstall FROM (SELECT sum(units) as dls, begindate, type FROM " + self.dbtable + " WHERE appleidentifier=" + str(appleid) + " AND (type=1 OR type='IA1')  AND begindate>\"" + offset + "\"GROUP BY begindate, type ORDER BY begindate DESC) as a LEFT OUTER JOIN (SELECT sum(units) as dls, begindate, type FROM " + self.dbtable + " WHERE appleidentifier=" + str(appleid) + " AND (type=7 OR type='IA7')  AND begindate>\"" + offset + "\"GROUP BY begindate, type ORDER BY begindate DESC) as b ON a.begindate=b.begindate"
        cursor.execute(query)
        detaillist = [["Date","NewDownloads","Reinstalls"]]
        result = cursor.fetchall()
        for line in result:
            linelist = []
            linelist.append(str(line[0]))
            linelist.append(str(line[1]))
            linelist.append(str(line[2]))
            detaillist.append(linelist)
        return detaillist
    def getItemNewDownloads(self, appleid, offset="0"):
        """ Returns a list of lists containing information on the number of
        new downloads of a given application id (Apple ID).  Information is
        grouped by date for worldwide downloads in order by date. Offset controls
        how far back in time data is shown returned for, it should be a unix
        timestamp as a string or a date string in the form of YYYY-MM-DD.

        The first list returned in the list contains headers with names of
        info in the lists that follow it.

        Example return

        [["Date", "NewDownloads"],["2010-02-22", "200"],["2010-02-23", "150"]]
        """
        cursor = self.db.cursor()
        query = "SELECT begindate, sum(units) FROM " + self.dbtable + " WHERE appleidentifier=" + str(appleid) + " AND (type=1 OR type='IA1') AND begindate>\"" + offset + "\"GROUP BY begindate ORDER BY begindate DESC"
        cursor.execute(query)
        detaillist = [["Date","NewDownloads"]]
        result = cursor.fetchall()
        for line in result:
            linelist = []
            linelist.append(str(line[0]))
            linelist.append(str(line[1]))
            detaillist.append(linelist)
        return detaillist
    def getItemRevenue(self, appleid, offset="0"):
        """ Returns a list of lists containing information on the revenue
        generated by a given application id (Apple ID).  Information is
        grouped by date and by currency type. Offset controls
        how far back in time data is shown returned for, it should be a unix
        timestamp as a string or a date string in the form of YYYY-MM-DD.

        The first list returned in the list contains headers with names of
        info in the lists that follow it.

        Example return

        [["Date","Revenue","Units Sold","Currency"],["2010-02-22", "20.70", "20","USD"],["2010-02-22", "15.30", "30","EUR"]]
        """
        cursor = self.db.cursor()
        query = "SELECT begindate, sum(royaltyprice * units), sum(units), royaltycurrency FROM " + self.dbtable + " WHERE appleidentifier=" + str(appleid) + " AND (type=1 OR type='IA1')  AND begindate>\"" + offset + "\"GROUP BY royaltycurrency, begindate ORDER BY begindate DESC"
        cursor.execute(query)
        detaillist = [["Date","Revenue","Units Sold","Currency"]]
        result = cursor.fetchall()
        for line in result:
            linelist = []
            linelist.append(str(line[0]))
            linelist.append(str(line[1]))
            linelist.append(str(line[2]))
            linelist.append(str(line[3]))
            detaillist.append(linelist)
        return detaillist
    def getItemCountryBreakdown(self, appleid, offset="0"):
        """ Returns a list of lists containing information on the total number of
        new downloads of a given application id (Apple ID).  Information is
        grouped by country for worldwide downloads. Offset controls
        how far back in time data is shown returned for, it should be a unix
        timestamp as a string or a date string in the form of YYYY-MM-DD.

        The first list returned in the list contains headers with names of
        info in the lists that follow it.

        Example return

        [["Country", "Units"],["US", "200"],["UK", "150"]]
        """
        cursor = self.db.cursor()
        query = "SELECT sum(units), countrycode FROM " + self.dbtable + " WHERE appleidentifier=" + str(appleid) + " AND (type=1 OR type='IA1')  AND begindate>\"" + offset + "\"GROUP BY countrycode"
        cursor.execute(query)
        detaillist = [["Country","Units"]]
        result = cursor.fetchall()
        for line in result:
            linelist = []
            linelist.append(str(line[1]))
            linelist.append(str(line[0]))
            detaillist.append(linelist)
        return detaillist
    def getItemName(self,appleid):
        """ Returns the vendor id associated with the apple id as a string

            Example Return:

            'iFart'
        """
        cursor = self.db.cursor()
        query = "SELECT vendorid FROM " + self.dbtable + " WHERE appleidentifier=" + str(appleid)
        cursor.execute(query)
        result = cursor.fetchall()
        return result[0][0]
    def getItemReinstalls(self, appleid, offset="0"):
        """ Returns a list of lists containing information on the number of
        reinstalls of a given application id (Apple ID).  Information is
        grouped by date for worldwide downloads in order by date. Offset controls
        how far back in time data is shown returned for, it should be a unix
        timestamp as a string or a date string in the form of YYYY-MM-DD.

        The first list returned in the list contains headers with names of
        info in the lists that follow it.

        Example return

        [["Date", "Reinstalls"],["2010-02-22", "200"],["2010-02-23", "150"]]
        """
        cursor = self.db.cursor()
        query = "SELECT begindate, sum(units) FROM " + self.dbtable + " WHERE appleidentifier=" + str(appleid) + " AND (type=7 OR type='IA7') AND begindate>\"" + offset + "\" GROUP BY begindate ORDER BY begindate DESC"
        cursor.execute(query)
        detaillist = [["Date","Reinstalls"]]
        result = cursor.fetchall()
        for line in result:
            linelist = []
            linelist.append(str(line[0]))
            linelist.append(str(line[1]))
            detaillist.append(linelist)
        return detaillist
    def displayMainMenu(self):
        """ Called to display the main menu if using the built in view layer,
            you should not need to use this unless you would like to customize
            the view layer further.
        """
        items = self.getItems()
        datalist = [["<b>App</b>","<b>Apple ID</b>","<b>Action</b>"]]
        for k,v in items.iteritems():
            itemlist = []
            itemlist.append(k)
            itemlist.append(v)
            actionurl = "<a href=\"" + self.baseurl + "?action=itemdetail&app=" + str(v) + "\">View</a>"
            itemlist.append(actionurl)
            datalist.append(itemlist)
        
        self.printHeader("pyTunesConnect Main Menu")
        self.printTable(datalist)
        self.printFooter()
    def getMaxDateForOffset(self, offset):
        cursor = self.db.cursor()
        query = "SELECT MAX(begindate) FROM " + self.dbtable
        cursor.execute(query)
        result = cursor.fetchall()
        datestring = str(result[0][0])
        components = re.split("-",datestring)
        maxdate = datetime.date(int(components[0]), int(components[1]), int(components[2]))
        timeoffset = timedelta(days=int(offset))
        returndate = maxdate - timeoffset
        return str(returndate)
    def displayItemDetail(self, appleid, offset):
        """ Called to display the item details page including charts. You
            shouldn't need to call this manually or edit it unless you
            would like to customize the view layer further.  appleid is the
            Apple ID of the item details should be displayed for. Offset controls
            how far back in time data is shown returned for, it should be a unix
            timestamp as a string or a date string in the form of YYYY-MM-DD.

            The code in this method is a good example of how to use the
            data and chart methods in the class.
        """
        #build re-install chart
        detail = self.getItemReinstalls(appleid, offset)
        head, body = pytunescharts.getDygraph(detail, 'itemchart', 1000, 480)
        #build new downloads chart
        newdetail = self.getItemNewDownloads(appleid, offset)
        head2, body2 = pytunescharts.getDygraph(newdetail, 'newchart', 1000, 480)
        #build country intesnity map chart
        geo = self.getItemCountryBreakdown(appleid, offset)
        geotypes = ["string", "number"]
        head4, body4 = pytunescharts.getGoogleIntensityMap(geo, "map", geotypes)
        #build java revenue table
        sales = self.getItemRevenue(appleid, offset)
        types = ["string", "number", "number", "string"]
        head3, body3 = pytunescharts.getGoogleTable(sales, 'saleschart', types)
        #build the HTML page before printing
        title = "App Details For " + self.getItemName(appleid)
        realhead = head + head3 + head4
        #print the HTML
        self.printHeader(title, realhead)
        print "<br>Viewing Data For: <b>" + self.stringOffset + " Days</b><br>"
        #build menu for selecting date range (offset)
        dateBaseURL = "<a href=\"" + self.baseurl + "?action=itemdetail&app=" + str(appleid) + "&offset="
        print "View data for:  " + dateBaseURL + "0\">All Days</a>   " + dateBaseURL + "31\">Last Month</a>   " + dateBaseURL + "7\">Last Week</a>   " + dateBaseURL + "1\">Last Day</a><br><br>"
        print "<br>New Downloads<br>" + body2 + '<br><br>Re-installs<br>' + body + "<br><br>Downloads by country<br>" + body4 + "<br><br>Revenue Data<br>" + body3
        self.printFooter()
    def printTable(self,data):
        """ This method will print a list of lists as a standard HTML table.
            This method is used by the view layer.
        """
        print "<br><table border=\"1\">"
        for row in data:
            print "<tr>"
            for column in row:
                print "<td>" + str(column) + "</td>"
            print "</tr>"
        print "</table><br>"
    def display(self):
        """ Called to invoke the built in view layer.
        """
        #required to generate docs (uncomment and set to allowed ip when generating docs)
        #ip = "173.8.254.5"
        
        #for normal operation (comment out to generate docs)
        ip = os.environ['REMOTE_ADDR']
        
        if ip not in self.allowedips:
            #if the ip of the user requesting the page isn't allowed show an error
            detail = "Access denied from " + ip
            self.printError("ACCESS DENIED",detail)
            return
        #get the fields from the URL
        form = cgi.FieldStorage()
        if "action" not in form:
            #if no "action" variable was in the form display the main menu
            self.displayMainMenu()
            return
        #get the action value from the form
        action = form["action"].value
        if(action=="itemdetail"):
            #if the action is itemdetail
            if "app" not in form:
                #checking for required variable
                self.printError("MISSING VARIABLE","'app' is a required variable")
                return
            #default offset to 0 (this will show all data and make it an optional parameter)
            offset = "0"
            self.stringOffset = "All"
            if "offset" in form:
                offset = form["offset"].value
                if(str(offset)=="0"):
                    #special case if offset is 0
                    self.stringOffset = "All"
                else:
                    self.stringOffset = "Last " + str(offset)
                    offset = self.getMaxDateForOffset(offset)
            #display the details for the item
            self.displayItemDetail(form["app"].value, offset)
        elif(action=="dlchartapi"):
            #a request was made to the chart web service
            #set all default values
            width = 640
            height = 480
            divname = "chart"
            charttype = "new"
            offset = "0"
            if "app" not in form:
                #check for required variables
                self.printError("MISSING VARIABLE","'app' is a required variable")
                return
            appleid = form["app"].value
            #change optional variables to what was passed if they are set
            if "width" in form:
                width = form["width"].value
            if "height" in form:
                height = form["height"].value
            if "divname" in form:
                divname = form["divname"].value
            if "type" in form:
                charttype = form["type"].value
            if "offset" in form:
                offset = form["offset"].value
                offset = self.getMaxDateForOffset(offset)
            #pull data from db based on charttype
            if(charttype=="new"):
                detail = self.getItemNewDownloads(appleid, offset)
            else:
                detail = self.getItemReinstalls(appleid, offset)
            #get the chart
            head, body = pytunescharts.getDygraph(detail, divname, height, width)
            #print the chart in the response
            print "Content-type: text/html\n"
            print body
    def printError(self, message, detail):
        """ Called by the view layer to print any errors as HTML
        """
        self.printHeader(message)
        print "<br>" + str(detail)
        self.printFooter()
    def printHeader(self, title, header=''):
        """ Used by the view layer to print a standardized HTML header across all pages
        """
        print "Content-type: text/html\n"
        print
        print "<html><title>" + str(title) + "</title><head>" + header + "</head><body><h1>" + str(title) + "</h1><br>"
    def printFooter(self):
        """ Used by the view layer to print a standard HTML footer across all pages.
        """
        print "<br><br><a href=\"" + self.baseurl + "\">Home</a><br></body></html>"
    def parseReport(self,fname):
        """ Used to parse the report downloaded by appdailysales.py and
            insert the data into the mySQL database.

            This will be called automatically when pytunesconnect.py is
            executed with the -d option at the command line.
        """

        #connect to database
	db = MySQLdb.connect(host=self.dbhost, user=self.dbuser, passwd=self.dbpass,db=self.dbname)
	cursor = db.cursor()

        #open the TSV file downloaded by appdailysales.py
	report = open(fname,"r")
	line = report.readline()
	#skip the first line of headers
	line = report.readline()
	while(line != ''):
                #while there are still lines to be read in the file split the line and pull out the values
		split = re.split('\t',line)
		vendorid = split[2]
		producttype = split[8]
		units = split[9]
		royaltyprice = split[10]
		begindate = split[11]
		beginSplit = re.split('/',begindate)
		begindate = beginSplit[2]+'-'+beginSplit[0]+'-'+beginSplit[1]
		enddate = split[12]
		endSplit = re.split('/',enddate)
		enddate = endSplit[2]+'-'+endSplit[0]+'-'+endSplit[1]
		customercurrency = split[13]
		countrycode = split[14]
		royaltycurrency = split[15]
		appleid = split[19]
		customerprice = split[20]

                #build the insert quesry
		query = "INSERT INTO " + self.dbtable + " (vendorid, type, units, royaltyprice, begindate, enddate, customercurrency, countrycode, royaltycurrency, appleidentifier, customerprice)"+" VALUES ('"+vendorid+"', '"+producttype+"', "+units+", "+royaltyprice+", '"+begindate+"', '"+enddate+"', '"+customercurrency+"', '"+countrycode+"', '"+royaltycurrency+"', "+appleid+", "+customerprice+")"
		cursor.execute(query)
		db.commit()
		line = report.readline()

	print "DAILY SALES REPORT IMPORT SUCCESSFUL"
	return



def main():
    me = Pytunesconnect()
    if '-d' in sys.argv:
        #download reports if running with -d flag
        if(len(sys.argv)!=4):
            print 'Error not enough arguments for report download.\nProper format: pytunesconnect.py -d username password'
        else:
            #setup the options for running appdailysales
            login = sys.argv[2]
            password = sys.argv[3]
            options = appdailysales.ReportOptions()
            options.appleId = login
            options.password = password
            options.outputDirectory = '/tmp'
            options.unzipFile = True
            options.verbose = False
            options.daysToDownload = 1
            options.dateToDownload = None
            try:
                filename = appdailysales.downloadFile(options)
                #if the report download was successful have pyTunesConnect parse it
                me.parseReport(filename[0])
            except:
                traceback.print_exc()
    else:
        #display with built in view layer
        me.display()


main()
